import sqlite3 from 'sqlite3';
import { fileURLToPath } from 'url';
import path from 'path';
import { ResultData } from './result.js';
// 获取当前文件的绝对路径
const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

// 连接到SQLite数据库
const db = new sqlite3.Database(path.join(__dirname, '../database.sqlite'), (err) => {
  if (err) {
    console.error('Error connecting to database:', err.message);
  } else {
    console.log('Connected to the SQLite database.');
  }
});

// 初始化数据库表
db.serialize(() => {
  const createTables = `
    CREATE TABLE IF NOT EXISTS students (
        StudentID INTEGER PRIMARY KEY AUTOINCREMENT,
        Name TEXT NOT NULL,
        Email TEXT UNIQUE NOT NULL,
        Password TEXT NOT NULL,
        Photo TEXT,
        CreateTime DATETIME DEFAULT CURRENT_TIMESTAMP,
        UpdateTime DATETIME DEFAULT CURRENT_TIMESTAMP,
        IsDeleted INTEGER DEFAULT 0
    );

    CREATE TABLE IF NOT EXISTS tags (
        TagID INTEGER PRIMARY KEY AUTOINCREMENT,
        Name TEXT NOT NULL,
        CreateTime DATETIME DEFAULT CURRENT_TIMESTAMP,
        IsDeleted INTEGER DEFAULT 0
    );  

    CREATE TABLE IF NOT EXISTS noticeboardPosts (
        PostID INTEGER PRIMARY KEY AUTOINCREMENT,
        Title TEXT NOT NULL,
        Content TEXT NOT NULL,
        StudentID INTEGER NOT NULL,
        TagID INTEGER,
        Media TEXT,
        LikeCount INTEGER DEFAULT 0,
        CommentCount INTEGER DEFAULT 0,
        IsHot INTEGER DEFAULT 0,
        CreateTime DATETIME DEFAULT CURRENT_TIMESTAMP,
        UpdateTime DATETIME DEFAULT CURRENT_TIMESTAMP,
        IsDeleted INTEGER DEFAULT 0,
        FOREIGN KEY (StudentID) REFERENCES students(StudentID),
        FOREIGN KEY (TagID) REFERENCES tags(TagID)
    );

    CREATE TABLE IF NOT EXISTS likes (
        LikeID INTEGER PRIMARY KEY AUTOINCREMENT,
        PostID INTEGER NOT NULL,
        StudentID INTEGER NOT NULL,
        CreateTime DATETIME DEFAULT CURRENT_TIMESTAMP,
        IsDeleted INTEGER DEFAULT 0,
        FOREIGN KEY (PostID) REFERENCES noticeboardPosts(PostID),
        FOREIGN KEY (StudentID) REFERENCES students(StudentID)
    );

    CREATE TABLE IF NOT EXISTS comments (
        CommentID INTEGER PRIMARY KEY AUTOINCREMENT,
        PostID INTEGER NOT NULL,
        StudentID INTEGER NOT NULL,
        Content TEXT NOT NULL,
        CreateTime DATETIME DEFAULT CURRENT_TIMESTAMP,
        IsDeleted INTEGER DEFAULT 0,
        FOREIGN KEY (PostID) REFERENCES noticeboardPosts(PostID),
        FOREIGN KEY (StudentID) REFERENCES students(StudentID)
    );
  `;

  db.exec(createTables, (err) => {
    if (err) {
      console.error('Error creating tables:', err.message);
    } else {
      console.log('Tables created successfully.');
    }
  });
});

// 枚举对象：定义数据库操作类型
export const DB_METHOD = Object.freeze({
  ALL: 'all',
  RUN: 'run',
  GET: 'get',
});

class SqlExecUtil {
  exec = (method, query, params = []) => {
    return new Promise((resolve, reject) => {
      try {
        console.log('Executing SQL:', query, params);
        db[method](query, params, (err, result) => {
          if (err) {
            console.error('Database query error:', err);
            reject(ResultData.error(err));
          } else {
            console.log('Query result:', result);
            resolve(result);
          }
        });
      } catch (err) {
        console.error('Unexpected error in sqlExecUtil:', err);
        reject(ResultData.error(err));
      }
    });
  };
  
  async all(query, params) {
    return await this.exec(DB_METHOD.ALL, query, params);
  }
  
  async getLimit(query, params, page = 1) {
    query += ' LIMIT ? OFFSET ?';
    const limit = 20;
    const offset = (page - 1) * limit;
    const list = await this.exec(DB_METHOD.ALL, query, [...params, limit, offset]);
    return {list, page: parseInt(page), limit};
  }

  async run(query, params) {
    return await this.exec(DB_METHOD.RUN, query, params);
  }

  async get(query, params) {
    return await this.exec(DB_METHOD.GET, query, params);
  }

  async beginTransaction() {
    return new Promise((resolve, reject) => {
      db.run('BEGIN TRANSACTION', (err) => {
        if (err) reject(err);
        else resolve();
      });
    });
  }

  async commit() {
    return new Promise((resolve, reject) => {
      db.run('COMMIT', (err) => {
        if (err) reject(err);
        else resolve();
      });
    });
  }

  async rollback() {
    return new Promise((resolve, reject) => {
      db.run('ROLLBACK', (err) => {
        if (err) reject(err);
        else resolve();
      });
    });
  }

  // 封装事务方法，用于在事务中执行操作，保证操作的原子性
  async withTransaction(callback) {
    try {
      await this.beginTransaction();
      const result = await callback();
      await this.commit();
      return result;
    } catch (error) {
      await this.rollback();
      throw error;
    }
  }
}

export const sqlUtil = new SqlExecUtil();
export default db;